﻿CREATE FUNCTION Collection_Stats(
	@LoanID int
)RETURNS TABLE RETURN
--DECLARE @LoanID int SET @LoanID = 1004872
SELECT
ISNULL(PTP.[PTPs:], 0) AS [PTPs:], ISNULL(PTP.[Open], 0) AS [Open]
, ISNULL(PTP.Paid, 0) AS Paid, ISNULL(PTP.Broken, 0) AS Broken
, ISNULL(NSF.Count,0) AS NSF
, ISNULL(PS.DPD30, 0) AS DPD30, ISNULL(PS.DPD60, 0) AS DPD60
, ISNULL(PS.DPD90, 0) AS [DPD90+], ISNULL(PS.DPDA, 0) AS DPDA
FROM (
SELECT NSF.Count FROM usix.t_CustomerFile_Export AS CF WITH(nolock)
INNER JOIN usix.t_PaymentNSF AS NSF ON CF.CompanyID_Raw = NSF.CompanyID AND CF.UsixID = NSF.[Id #]
WHERE     (CF.LoanID = @LoanID)
) NSF
 FULL OUTER JOIN
                      dbo.Collection_PromiseToPay(@LoanID) AS PTP ON 1 = 1 FULL OUTER JOIN
                      dbo.Collection_PaymentStats(@LoanID) AS PS ON 1 = 1



